// 表结构适配器 - 用于适配不同的数据库表结构
class TableAdapter {
  constructor() {
    this.tableMappings = {
      // 闲置物品表映射 - 基于shared_items表
      idleItems: {
        // 可能的表名
        possibleNames: ['shared_items', 'idle_items', 'idleitems', 'items', 'community_items', 'neighborhood_items'],
        // 字段映射 - 基于shared_items表结构
        fieldMappings: {
          id: ['id', 'item_id', 'ID'],
          itemName: ['name', 'item_name', 'title', 'itemName'],
          itemCategory: ['category', 'item_category', 'type', 'itemCategory'],
          itemDesc: ['description', 'item_desc', 'desc', 'itemDesc'],
          borrowDuration: ['borrow_duration', 'available_to', 'duration', 'borrowDuration'], // 可借用时长
          contactInfo: ['contact_info', 'contact', 'phone', 'contactInfo'], // 联系信息
          borrowCondition: ['borrow_condition', 'condition', 'borrowCondition'], // 借用条件
          deposit: ['deposit', 'deposit_amount'], // 押金
          // shared_items表中没有图片字段，将在transformRow中提供默认值
          status: ['status', 'state'], // 状态字段
          createTime: ['created_at', 'create_time', 'createTime'] // 创建时间
        }
      },
      // 技能共享表映射 - 基于shared_skills表
      skills: {
        possibleNames: ['shared_skills'],
        fieldMappings: {
          id: ['skill_id', 'id'],
          name: ['skill_name', 'name'],
          category: ['skill_category', 'category'],
          description: ['skill_description', 'description'],
          serviceMethod: ['service_method', 'serviceMethod'],
          contactInfo: ['contact_info', 'contactInfo'],
          serviceTime: ['service_time', 'serviceTime'],
          serviceArea: ['service_area', 'serviceArea', 'area'],
          price: ['price', 'hourly_rate', 'rate'],
          experience: ['experience_years', 'experience', 'years'],
          // 字段映射已恢复，基于用户提供的数据库表结构
          status: ['status'],
          createTime: ['created_at', 'createTime']
        }
      },
      // 服务分类表映射
      serviceCategories: {
        possibleNames: ['service_categories', 'categories', 'service_cats'],
        fieldMappings: {
          id: ['category_id', 'id', 'ID'],
          name: ['category_name', 'name', 'title'],
          parentId: ['parent_category_id', 'parent_id', 'parentId'],
          createTime: ['category_id', 'create_time', 'created_at', 'createTime']
        }
      },
      // 用户表映射
      users: {
        possibleNames: ['users', 'user', 'members'],
        fieldMappings: {
          id: ['user_id', 'id', 'ID'],
          username: ['username', 'name', 'user_name'],
          phone: ['phone_number', 'phone', 'contact'],
          isMember: ['is_member', 'member', 'isMember'],
          points: ['points_balance', 'points', 'balance'],
          createTime: ['created_at', 'create_time', 'createTime']
        }
      },
      // 社区通知表映射
      notices: {
        possibleNames: ['community_notices', 'notices', 'announcements'],
        fieldMappings: {
          id: ['notice_id', 'id', 'ID'],
          title: ['title', 'name'],
          content: ['content', 'description', 'desc'],
          publisher: ['publisher', 'author', 'publisher_name'],
          type: ['notice_type', 'type', 'category'],
          isUrgent: ['is_urgent', 'urgent', 'isUrgent'],
          isActive: ['is_active', 'active', 'isActive'],
          createTime: ['created_at', 'create_time', 'createTime']
        }
      }
    };
  }

  // 查找表名
  async findTableName(connection, tableType) {
    const mapping = this.tableMappings[tableType];
    if (!mapping) {
      throw new Error(`未知的表类型: ${tableType}`);
    }

    const [tables] = await connection.execute('SHOW TABLES');
    const tableNames = tables.map(table => Object.values(table)[0]);

    for (const possibleName of mapping.possibleNames) {
      const found = tableNames.find(name => 
        name.toLowerCase() === possibleName.toLowerCase()
      );
      if (found) {
        return found;
      }
    }

    // 如果没有找到精确匹配，尝试模糊匹配
    for (const tableName of tableNames) {
      for (const possibleName of mapping.possibleNames) {
        if (tableName.toLowerCase().includes(possibleName.toLowerCase()) ||
            possibleName.toLowerCase().includes(tableName.toLowerCase())) {
          return tableName;
        }
      }
    }

    throw new Error(`未找到 ${tableType} 相关的表`);
  }

  // 获取字段映射
  async getFieldMapping(connection, tableName, tableType) {
    const mapping = this.tableMappings[tableType];
    if (!mapping) {
      throw new Error(`未知的表类型: ${tableType}`);
    }

    const [columns] = await connection.execute(`DESCRIBE ${tableName}`);
    console.log(`表 ${tableName} 的列信息:`, columns);
    const fieldMapping = {};

    // 为每个标准字段找到对应的数据库字段
    for (const [standardField, possibleNames] of Object.entries(mapping.fieldMappings)) {
      let found = false;
      for (const possibleName of possibleNames) {
        const column = columns.find(col => 
          col.Field.toLowerCase() === possibleName.toLowerCase()
        );
        if (column) {
          fieldMapping[standardField] = column.Field;
          found = true;
          console.log(`标准字段 ${standardField} 映射到数据库字段 ${column.Field}`);
          break;
        }
      }
      if (!found) {
        // 如果没有找到匹配的字段，使用第一个可能的名称作为默认值
        fieldMapping[standardField] = possibleNames[0];
        console.warn(`未找到标准字段 ${standardField} 对应的数据库字段，使用默认值 ${possibleNames[0]}`);
      }
    }

    console.log('最终字段映射:', fieldMapping);
    return fieldMapping;
  }

  // 转换查询结果
  transformResult(data, fieldMapping) {
    if (Array.isArray(data)) {
      return data.map(row => this.transformRow(row, fieldMapping));
    }
    return this.transformRow(data, fieldMapping);
  }

  // 格式化日期函数（处理时区问题）
  formatDate(dateValue) {
    if (!dateValue) return '';
    
    let date;
    // 处理字符串格式的日期
    if (typeof dateValue === 'string') {
      // 检查是否包含T
      if (dateValue.includes('T')) {
        // 处理ISO格式日期，将其视为UTC时间并转换为本地时间
        date = new Date(dateValue);
        // 手动构建本地时间字符串，避免toISOString()转换为UTC
        const year = date.getFullYear();
        const month = String(date.getMonth() + 1).padStart(2, '0');
        const day = String(date.getDate()).padStart(2, '0');
        const hours = String(date.getHours()).padStart(2, '0');
        const minutes = String(date.getMinutes()).padStart(2, '0');
        const seconds = String(date.getSeconds()).padStart(2, '0');
        return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
      } else if (dateValue.match(/^\d{4}-\d{2}-\d{2}$/)) {
        // 仅日期格式 YYYY-MM-DD
        return dateValue + ' 00:00:00';
      } else if (dateValue.match(/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/)) {
        // 已经是正确格式
        return dateValue;
      } else {
        // 无法识别的格式，原样返回
        return dateValue;
      }
    } else if (dateValue instanceof Date) {
      // 直接处理Date对象，使用本地时间
      const year = dateValue.getFullYear();
      const month = String(dateValue.getMonth() + 1).padStart(2, '0');
      const day = String(dateValue.getDate()).padStart(2, '0');
      const hours = String(dateValue.getHours()).padStart(2, '0');
      const minutes = String(dateValue.getMinutes()).padStart(2, '0');
      const seconds = String(dateValue.getSeconds()).padStart(2, '0');
      return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
    } else {
      // 不是日期类型，原样返回
      return dateValue;
    }
  }
  
  // 转换单行数据
  transformRow(row, fieldMapping) {
    const transformed = {};
    console.log('原始行数据:', row);
    console.log('字段映射:', fieldMapping);
    
    // 遍历字段映射，从原始数据中获取值
    for (const [standardField, dbField] of Object.entries(fieldMapping)) {
      console.log(`尝试获取标准字段 ${standardField}，对应数据库字段 ${dbField}`);
      
      // 优先尝试使用数据库字段名
      if (row.hasOwnProperty(dbField)) {
        console.log(`找到数据库字段 ${dbField}，值为: ${row[dbField]}`);
        
        // 处理日期格式字段
        if (['createTime'].includes(standardField)) {
          transformed[standardField] = this.formatDate(row[dbField]);
        } else if (standardField === 'borrowDuration') {
          // 确保borrowDuration作为数值处理
          transformed[standardField] = parseInt(row[dbField]) || row[dbField];
        } else if (standardField === 'deposit') {
          // 确保deposit作为数值处理
          transformed[standardField] = parseFloat(row[dbField]) || row[dbField];
        } else if (standardField === 'contactInfo') {
          // 确保contactInfo作为字符串处理
          transformed[standardField] = row[dbField].toString() || row[dbField];
        } else if (standardField === 'borrowCondition') {
          // 确保borrowCondition作为字符串处理
          transformed[standardField] = row[dbField].toString() || row[dbField];
        } else if (standardField === 'serviceMethod') {
          // 直接返回数据库中的服务方式文本
          transformed[standardField] = row[dbField];
        } else {
          transformed[standardField] = row[dbField];
        }
      } else if (row.hasOwnProperty(standardField)) {
        // 如果数据库字段名不存在，尝试使用标准字段名
        console.log(`找到标准字段 ${standardField}，值为: ${row[standardField]}`);
        
        // 处理日期格式字段
        if (['createTime'].includes(standardField)) {
          transformed[standardField] = this.formatDate(row[standardField]);
        } else if (standardField === 'borrowDuration') {
          // 确保borrowDuration作为数值处理
          transformed[standardField] = parseInt(row[standardField]) || row[standardField];
        } else if (standardField === 'deposit') {
          // 确保deposit作为数值处理
          transformed[standardField] = parseFloat(row[standardField]) || row[standardField];
        } else if (standardField === 'contactInfo') {
          // 确保contactInfo作为字符串处理
          transformed[standardField] = row[standardField].toString() || row[standardField];
        } else if (standardField === 'borrowCondition') {
          // 确保borrowCondition作为字符串处理
          transformed[standardField] = row[standardField].toString() || row[standardField];
        } else {
          transformed[standardField] = row[standardField];
        }
      } else {
        console.warn(`字段 ${standardField}/${dbField} 不存在于行数据中`);
        // 为特定字段设置合理的默认值
        if (standardField === 'imageUrl') {
          transformed[standardField] = '/img/tabbar_resource.png'; // 提供默认图片
        } else if (standardField === 'deposit') {
          transformed[standardField] = 0; // 押金默认为0
        } else if (standardField === 'contactInfo') {
          transformed[standardField] = '联系方式未知'; // 联系信息默认值
        } else if (standardField === 'borrowCondition') {
          transformed[standardField] = '无特殊条件'; // 借用条件默认值
        } else {
          transformed[standardField] = '未知';
        }
      }
    }
    
    // 为shared_items表添加默认图片
    if (!transformed.imageUrl) {
      transformed.imageUrl = '/img/tabbar_resource.png';
    }
    
    console.log('转换后的数据:', transformed);
    return transformed;
  }

  // 构建查询字段
  buildSelectFields(fieldMapping) {
    return Object.entries(fieldMapping)
      .map(([standardField, dbField]) => {
        return `${dbField} as ${standardField}`;
      })
      .join(', ');
  }

  // 为services表创建模拟的闲置物品和技能数据
  async createMockData(connection, tableName) {
    // 检查是否是services表
    if (tableName.toLowerCase() !== 'services') {
      return null;
    }

    // 获取服务分类
    const [categories] = await connection.execute('SELECT * FROM service_categories');
    
    // 为闲置物品创建模拟数据
    const idleItemsData = [
      {
        itemName: '工具箱',
        itemCategory: '工具类',
        itemDesc: '包含电钻、螺丝刀等常用工具，可借用',
        borrowDuration: 7,
        contactInfo: '13800138001',
        borrowCondition: '需要押金100元',
        imageUrl: '/img/tabbar_resource.png',
        status: 'approved'
      },
      {
        itemName: '折叠梯子',
        itemCategory: '工具类',
        itemDesc: '铝合金折叠梯，最高2米，适合家庭使用',
        borrowDuration: 3,
        contactInfo: '13900139002',
        borrowCondition: '需要押金50元',
        imageUrl: '/img/tabbar_resource.png',
        status: 'approved'
      },
      {
        itemName: '婴儿推车',
        itemCategory: '母婴用品',
        itemDesc: '轻便型婴儿推车，适合0-3岁宝宝',
        borrowDuration: 14,
        contactInfo: '13600136003',
        borrowCondition: '免费借用，请爱护',
        imageUrl: '/img/tabbar_resource.png',
        status: 'approved'
      }
    ];

    // 为技能共享创建模拟数据
    const skillsData = [
      {
        name: '电脑维修',
        category: '技术服务',
        description: '提供电脑软硬件维修服务，经验丰富',
        serviceMethod: '上门服务',
        contactInfo: '13500135004',
        serviceTime: '工作日晚上和周末',
        status: 'published'
      },
      {
        name: '英语辅导',
        category: '教育培训',
        description: '英语专业毕业，可提供小学到高中英语辅导',
        serviceMethod: '线上/线下',
        contactInfo: '13700137005',
        serviceTime: '周末全天',
        status: 'published'
      },
      {
        name: '手工编织',
        category: '手工艺术',
        description: '擅长毛衣、围巾等编织，可教授编织技巧',
        serviceMethod: '线下教学',
        contactInfo: '13400134006',
        serviceTime: '每周三下午',
        status: 'published'
      }
    ];

    return {
      idleItems: idleItemsData,
      skills: skillsData
    };
  }
}

module.exports = TableAdapter;